package com.ruoyi.generator.mapper;

import java.util.Date;
import java.util.List;

import org.beetl.sql.core.annotatoin.SqlResource;
import org.beetl.sql.core.db.OracleStyle;
import org.beetl.sql.core.db.SqlServerStyle;
import org.beetl.sql.core.mapper.BaseMapper;

import com.ruoyi.core.database.SqlParam;
import com.ruoyi.generator.domain.GenTableColumn;


/**
 * 业务字段 数据层
 * 
 * @author ruoyi
 */
@SqlResource("system.gentablecolumn")
public interface GenTableColumnMapper extends BaseMapper<GenTableColumn>
{
    /**
     * 根据表名称查询列信息
     * 
     * @param tableName 表名称
     * @return 列信息
     */
    public default List<GenTableColumn> selectDbTableColumnsByName(String tableName){
    	if(this.getSQLManager().getDbStyle() instanceof OracleStyle) {
    		return this.getSQLManager().execute("select lower(temp.column_name) as column_name,\r\n" + 
    				"                (case when (temp.nullable = 'N'  and  temp.constraint_type != 'P') then '1' else null end) as is_required,\r\n" + 
    				"                (case when temp.constraint_type = 'P' then '1' else '0' end) as is_pk,\r\n" + 
    				"                temp.column_id as sort,\r\n" + 
    				"                temp.comments as column_comment,\r\n" + 
    				"                (case when temp.constraint_type = 'P' then '1' else '0' end) as is_increment,\r\n" + 
    				"                lower(temp.data_type) as column_type\r\n" + 
    				"           from (\r\n" + 
    				"                  select col.column_id, col.column_name,col.nullable, col.data_type, colc.comments, uc.constraint_type\r\n" + 
    				"                       , row_number() over (partition by col.column_name order by uc.constraint_type desc) as row_flg\r\n" + 
    				"                  from user_tab_columns col\r\n" + 
    				"                  left join user_col_comments colc on colc.table_name = col.table_name and colc.column_name = col.column_name\r\n" + 
    				"                  left join user_cons_columns ucc on ucc.table_name = col.table_name and ucc.column_name = col.column_name\r\n" + 
    				"                  left join user_constraints uc on uc.constraint_name = ucc.constraint_name\r\n" + 
    				"                 where col.table_name = upper(#tableName#)\r\n" + 
    				"                  ) temp\r\n" + 
    				"           WHERE temp.row_flg = 1\r\n" + 
    				"          ORDER BY temp.column_id", GenTableColumn.class, SqlParam.create().set("tableName", tableName));
    	}
    	if(this.getSQLManager().getDbStyle() instanceof SqlServerStyle) {
    		return this.getSQLManager().execute("SELECT\r\n" + 
    				"            cast(A.NAME as nvarchar) as column_name,\r\n" + 
    				"            cast(B.NAME as nvarchar) + (case when B.NAME ='numeric' then '('+cast(A.prec as nvarchar)+','+cast(A.scale as nvarchar)+')' else '' end) as column_type,\r\n" + 
    				"            cast(G.[VALUE] as nvarchar) as column_comment,\r\n" + 
    				"            (SELECT 1  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE Z  WHERE TABLE_NAME=D.NAME and A.NAME = Z.column_name  ) as is_pk,\r\n" + 
    				"            colorder as sort\r\n" + 
    				"        FROM   SYSCOLUMNS   A\r\n" + 
    				"            LEFT   JOIN   SYSTYPES   B   ON   A.XTYPE=B.XUSERTYPE\r\n" + 
    				"            INNER   JOIN   SYSOBJECTS   D   ON   A.ID=D.ID     AND   D.XTYPE='U'   AND     D.NAME!='DTPROPERTIES'\r\n" + 
    				"            LEFT   JOIN   SYS.EXTENDED_PROPERTIES G   ON   A.ID=G.MAJOR_ID   AND   A.COLID=G.MINOR_ID\r\n" + 
    				"            LEFT   JOIN   SYS.EXTENDED_PROPERTIES F   ON   D.ID=F.MAJOR_ID   AND   F.MINOR_ID   =0\r\n" + 
    				"        where   D.NAME = #tableName#\r\n" + 
    				"        ORDER   BY   A.COLORDER", GenTableColumn.class, SqlParam.create().set("tableName", tableName));
    	}
    	
    	return this.getSQLManager().execute("select column_name, "
    			+ "(case when (is_nullable = 'no' && column_key != 'PRI') then '1' else null end) as is_required, "
				+ "(case when column_key = 'PRI' then '1' else '0' end) as is_pk, ordinal_position as sort, column_comment, "
				+ "(case when extra = 'auto_increment' then '1' else '0' end) as is_increment, column_type\r\n" + 
				"		from information_schema.columns where table_schema = (select database()) and table_name = (#tableName#)\r\n" + 
				"		order by ordinal_position", GenTableColumn.class, SqlParam.create().set("tableName", tableName));
    	
    }
    
    /**
     * 查询业务字段列表
     * 
     * @param genTableColumn 业务字段信息
     * @return 业务字段集合
     */
    public default List<GenTableColumn> selectGenTableColumnListByTableId(GenTableColumn genTableColumn){
    	return this.getSQLManager().execute("select * from gen_table_column where table_id = #tableId# order by sort", GenTableColumn.class, genTableColumn);
    }

    /**
     * 新增业务字段
     * 
     * @param genTableColumn 业务字段信息
     * @return 结果
     */
    public default int insertGenTableColumn(GenTableColumn genTableColumn) {
    	genTableColumn.setCreateTime(new Date());
    	 this.insert(genTableColumn,true);
    	 return 1;
    }

    /**
     * 修改业务字段
     * 
     * @param genTableColumn 业务字段信息
     * @return 结果
     */
    public default int updateGenTableColumn(GenTableColumn genTableColumn) {
    	genTableColumn.setUpdateTime(new Date());
    	return this.updateTemplateById(genTableColumn);
    }

    /**
     * 批量删除业务字段
     * 
     * @param ids 需要删除的数据ID
     * @return 结果
     */
    public default int deleteGenTableColumnByIds(Long[] ids) {
    	return this.getSQLManager().executeUpdate(" delete from gen_table_column where table_id in (#join(ids)#)", SqlParam.create().set("ids", ids));
    }
}